IF OBJECT_ID('dbo.GetRptSponsors') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetRptSponsors
    IF OBJECT_ID('dbo.GetRptSponsors') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetRptSponsors >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetRptSponsors >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetRptSponsors
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 7/20/2007
--
-- Description: Returns list of Sponsors from dbo.AdmSponsor
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetRptSponsors()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/20/07     KC        Created
-- 8/14/07     KC        Changed Name of Proc
-- 9/18/07     KC        Added @Name as Param
-- 12/11/08    CB	     Added column for infoed searches
-- =======================================================================
CREATE PROCEDURE dbo.GetRptSponsors
     (@Name                        VARCHAR(50)=NULL)
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     SponsorId           INT
     ShortName           VARCHAR             
     LongName            VARCHAR
     InfoEdCode          VARCHAR
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT
               
     -- Result Query    
     SELECT DISTINCT
          Id AS SponsorId,
          ShortName AS ShortName,
          Name AS LongName,
          InfoEdSponCode AS InfoEdSponCode
     FROM dbo.AdmSponsor
     WHERE     ((RTRIM(ShortName) LIKE '%' + @Name + '%') OR @Name IS NULL)
     ORDER BY ShortName
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetRptSponsors') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetRptSponsors >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetRptSponsors >>>'
go

GRANT EXECUTE ON dbo.GetRptSponsors TO InsightReport,Insight
go
